1 -- phpMyAdmin SQL Dump
2 -- version 4.8.3
3 -- https://www.phpmyadmin.net/
4 --
5 -- Host: 127.0.0.1
6 -- Generation Time: May 22, 2019 at 04:02 PM
7 -- Server version: 10.1.36-MariaDB
8 -- PHP Version: 7.2.11
9
10 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11 SET AUTOCOMMIT = 0;
12 START TRANSACTION;
13 SET time_zone = "+00:00";
14
15
16 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
17 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
18 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
19 /*!40101 SET NAMES utf8mb4 */;
20
21 --
22 -- Database: `petshop_management`
23 --
24
25 DELIMITER $$
26 --
27 -- Procedures
28 --
29 CREATE DEFINER=`root`@`localhost` PROCEDURE `calculations_for_pets` (IN `pid` VARCHAR(9), IN `sid` VARCHAR(9)) NO SQL
30 BEGIN
31 DECLARE
32 cpid ,csid int DEFAULT 0;
33 set cpid=(select cost from pets where pet_id=pid);
34 set csid=(select total from sales_details where sd_id=sid);
35 set csid=csid+cpid;
36 update sales_details set total=csid where sd_id=sid;
37 end$$
38
39 CREATE DEFINER=`root`@`localhost` PROCEDURE `calculations_for_product` (IN `ppid` VARCHAR(9), IN `sid` VARCHAR(9), IN `qnty` INT(11)) NO SQL
40 BEGIN
41 DECLARE
42 cppid ,csid int DEFAULT 0;
43 set cppid=(select cost from pet_products where pp_id=ppid);
44 set csid=(select total from sales_details where sd_id=sid);
45 set csid=csid+qnty*cppid;
46 update sales_details set total=csid where sd_id=sid;
47 end$$
48
49 DELIMITER ;
50
51 -- --------------------------------------------------------
52
53 --
54 -- Table structure for table `animals`
55 --
56
57 CREATE TABLE `animals` (
58 `pet_id` varchar(9) NOT NULL,
59 `breed` varchar(30) NOT NULL,
60 `weight` float NOT NULL,
61 `height` float NOT NULL,
62 `age` int(11) NOT NULL,
63 `fur` varchar(15) NOT NULL
64 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
65
66 --
67 -- Dumping data for table `animals`
68 --
69
70 INSERT INTO `animals` (`pet_id`, `breed`, `weight`, `height`, `age`, `fur`) VALUES
71 ('pa01', 'labrador', 11.3, 30, 2, 'white'),
72 ('pa02', 'parsian', 3.6, 20, 2, 'white'),
73 ('pa03', 'golden retriever', 12.5, 40, 2, 'gloden'),
74 ('pa04', 'boxer', 11.5, 45, 3, 'black'),
75 ('pa05', 'rag doll', 2.6, 20, 5, 'white'),
76 ('pa06', 'st bernard', 10.8, 35, 3, 'brownish yellow'),
77 ('pa07', 'bulldog', 8, 25, 3, 'white');
78
79 -- --------------------------------------------------------
80
81 --
82 -- Table structure for table `birds`
83 --
84
85 CREATE TABLE `birds` (
86 `pet_id` varchar(9) NOT NULL,
87 `type` varchar(25) NOT NULL,
88 `noise` varchar(10) NOT NULL
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
90
91 --
92 -- Dumping data for table `birds`
93 --
94
95 INSERT INTO `birds` (`pet_id`, `type`, `noise`) VALUES
96 ('pb01', 'grey parrot', 'moderate'),
97 ('pb02', 'black cheeked', 'low'),
98 ('pb03', 'grey headed', 'moderate'),
99 ('pb04', 'lilian', 'moderate'),
100 ('pb05', 'white cockatoo', 'moderate');
101
102 -- --------------------------------------------------------
103
104 --
105 -- Table structure for table `customer`
106 --
107
108 CREATE TABLE `customer` (
109 `cs_id` varchar(9) NOT NULL,
110 `cs_fname` varchar(10) NOT NULL,
111 `cs_minit` varchar(10) NOT NULL,
112 `cs_lname` varchar(10) NOT NULL,
113 `cs_address` varchar(30) NOT NULL
114 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
115
116 --
117 -- Dumping data for table `customer`
118 --
119
120 INSERT INTO `customer` (`cs_id`, `cs_fname`, `cs_minit`, `cs_lname`, `cs_address`) VALUES
121 ('cs01', 'Naveen', 'kumar', 'k', 'Mandya'),
122 ('cs02', 'manjunath', 'kumar', 'h v', 'BENGALURU'),
123 ('cs03', 'pavan', 'chikkanna', 'gowda', 'BENGALURU'),
124 ('cs04', 'kushal', 'kumar', 'k', 'BENGALURU'),
125 ('cs05', 'ravi', 'shankar', 'c', 'BENGALURU');
126
127 -- --------------------------------------------------------
128
129 --
130 -- Table structure for table `pets`
131 --
132
133 CREATE TABLE `pets` (
134 `pet_id` varchar(9) NOT NULL,
135 `pet_category` varchar(15) NOT NULL,
136 `cost` int(11) NOT NULL
137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
138
139 --
140 -- Dumping data for table `pets`
141 --
142
143 INSERT INTO `pets` (`pet_id`, `pet_category`, `cost`) VALUES
144 ('pa01', 'dog', 8000),
145 ('pa02', 'cat', 3000),
146 ('pa03', 'dog', 8500),
147 ('pa04', 'dog', 15000),
148 ('pa05', 'cat', 3500),
149 ('pa06', 'dog', 10500),
150 ('pa07', 'dog', 12000),
151 ('pb01', 'parrot', 2000),
152 ('pb02', 'lovebirds', 800),
153 ('pb03', 'lovebirds', 600),
154 ('pb04', 'lovebirds', 800),
155 ('pb05', 'cockatoo', 10000);
156
157 --
158 -- Triggers `pets`
159 --
160 DELIMITER $$
161 CREATE TRIGGER `check_sold` BEFORE UPDATE ON `pets` FOR EACH ROW BEGIN
162 DECLARE
163 checking int;
164 set checking=(select count(*) from sold_pets where pet_id=old.pet_id);
165 if (checking > 0) then
166 signal sqlstate '45000' set message_text = 'cannot update sold pet';
167 end if;
168 END
169 $$
170 DELIMITER ;
171
172 -- --------------------------------------------------------
173
174 --
175 -- Table structure for table `pet_products`
176 --
177
178 CREATE TABLE `pet_products` (
179 `pp_id` varchar(9) NOT NULL,
180 `pp_name` varchar(30) NOT NULL,
181 `pp_type` varchar(20) NOT NULL,
182 `cost` int(11) NOT NULL,
183 `belongs_to` varchar(20) NOT NULL
184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
185
186 --
187 -- Dumping data for table `pet_products`
188 --
189
190 INSERT INTO `pet_products` (`pp_id`, `pp_name`, `pp_type`, `cost`, `belongs_to`) VALUES
191 ('pp01', 'dog collar', 'accesories', 500, 'dog'),
192 ('pp02', 'chain', 'accesories', 100, 'cat'),
193 ('pp03', 'pedigree', 'food', 1500, 'dog'),
194 ('pp04', 'mouth mask', 'accesories', 250, 'dog'),
195 ('pp05', 'food bowl', 'accesories', 250, 'dog '),
196 ('pp06', 'bird feeds', 'food', 300, 'birds');
197
198 -- --------------------------------------------------------
199
200 --
201 -- Table structure for table `phone`
202 --
203
204 CREATE TABLE `phone` (
205 `cs_id` varchar(9) NOT NULL,
206 `cs_phone` bigint(10) NOT NULL
207 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
208
209 --
210 -- Dumping data for table `phone`
211 --
212
213 INSERT INTO `phone` (`cs_id`, `cs_phone`) VALUES
214 ('cs01', 8867762336),
215 ('cs01', 9902587276),
216 ('cs03', 9845034784),
217 ('cs04', 6361261639),
218 ('cs05', 86660873855);
219
220 -- --------------------------------------------------------
221
222 --
223 -- Table structure for table `sales_details`
224 --
225
226 CREATE TABLE `sales_details` (
227 `sd_id` varchar(9) NOT NULL,
228 `cs_id` varchar(9) NOT NULL,
229 `date` date NOT NULL,
230 `total` int(11) NOT NULL
231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
232
233 --
234 -- Dumping data for table `sales_details`
235 --
236
237 INSERT INTO `sales_details` (`sd_id`, `cs_id`, `date`, `total`) VALUES
238 ('sd01', 'cs03', '2018-10-26', 9500),
239 ('sd02', 'cs01', '2018-11-01', 3000),
240 ('sd03', 'cs03', '2018-11-08', 500),
241 ('sd04', 'cs04', '2018-11-15', 12250),
242 ('sd05', 'cs02', '2018-11-17', 9350),
243 ('sd06', 'cs05', '2018-11-20', 1900),
244 ('sd07', 'cs03', '2018-12-08', 10000);
245
246 -- --------------------------------------------------------
247
248 --
249 -- Table structure for table `sold_pets`
250 --
251
252 CREATE TABLE `sold_pets` (
253 `sd_id` varchar(9) NOT NULL,
254 `pet_id` varchar(9) NOT NULL
255 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
256
257 --
258 -- Dumping data for table `sold_pets`
259 --
260
261 INSERT INTO `sold_pets` (`sd_id`, `pet_id`) VALUES
262 ('sd01', 'pa01'),
263 ('sd02', 'pa02'),
264 ('sd04', 'pa07'),
265 ('sd05', 'pa03'),
266 ('sd06', 'pb02'),
267 ('sd06', 'pb04');
268
269 -- --------------------------------------------------------
270
271 --
272 -- Table structure for table `sold_products`
273 --
274
275 CREATE TABLE `sold_products` (
276 `sd_id` varchar(9) NOT NULL,
277 `pp_id` varchar(9) NOT NULL,
278 `quantity` int(11) NOT NULL
279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
280
281 --
282 -- Dumping data for table `sold_products`
283 --
284
285 INSERT INTO `sold_products` (`sd_id`, `pp_id`, `quantity`) VALUES
286 ('sd01', 'pp03', 1),
287 ('sd03', 'pp01', 1),
288 ('sd04', 'pp04', 1),
289 ('sd05', 'pp05', 1),
290 ('sd05', 'pp06', 2),
291 ('sd06', 'pp06', 1);
292
293 --
294 -- Indexes for dumped tables
295 --
296
297 --
298 -- Indexes for table `animals`
299 --
300 ALTER TABLE `animals`
301 ADD PRIMARY KEY (`pet_id`);
302
303 --
304 -- Indexes for table `birds`
305 --
306 ALTER TABLE `birds`
307 ADD PRIMARY KEY (`pet_id`);
308
309 --
310 -- Indexes for table `customer`
311 --
312 ALTER TABLE `customer`
313 ADD PRIMARY KEY (`cs_id`);
314
315 --
316 -- Indexes for table `pets`
317 --
318 ALTER TABLE `pets`
319 ADD PRIMARY KEY (`pet_id`);
320
321 --
322 -- Indexes for table `pet_products`
323 --
324 ALTER TABLE `pet_products`
325 ADD PRIMARY KEY (`pp_id`);
326
327 --
328 -- Indexes for table `phone`
329 --
330 ALTER TABLE `phone`
331 ADD PRIMARY KEY (`cs_id`,`cs_phone`);
332
333 --
334 -- Indexes for table `sales_details`
335 --
336 ALTER TABLE `sales_details`
337 ADD PRIMARY KEY (`sd_id`,`cs_id`),
338 ADD KEY `cs_id` (`cs_id`);
339
340 --
341 -- Indexes for table `sold_pets`
342 --
343 ALTER TABLE `sold_pets`
344 ADD PRIMARY KEY (`pet_id`),
345 ADD KEY `sd_id` (`sd_id`);
346
347 --
348 -- Indexes for table `sold_products`
349 --
350 ALTER TABLE `sold_products`
351 ADD PRIMARY KEY (`sd_id`,`pp_id`),
352 ADD KEY `sold_products_ibfk_2` (`pp_id`);
353
354 --
355 -- Constraints for dumped tables
356 --
357
358 --
359 -- Constraints for table `animals`
360 --
361 ALTER TABLE `animals`
362 ADD CONSTRAINT `animals_ibfk_1` FOREIGN KEY (`pet_id`) REFERENCES `pets` (`pet_id`) ON DELETE CASCADE;
363
364 --
365 -- Constraints for table `birds`
366 --
367 ALTER TABLE `birds`
368 ADD CONSTRAINT `birds_ibfk_1` FOREIGN KEY (`pet_id`) REFERENCES `pets` (`pet_id`) ON DELETE CASCADE;
369
370 --
371 -- Constraints for table `phone`
372 --
373 ALTER TABLE `phone`
374 ADD CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`cs_id`) REFERENCES `customer` (`cs_id`) ON DELETE CASCADE;
375
376 --
377 -- Constraints for table `sales_details`
378 --
379 ALTER TABLE `sales_details`
380 ADD CONSTRAINT `sales_details_ibfk_1` FOREIGN KEY (`cs_id`) REFERENCES `customer` (`cs_id`) ON DELETE CASCADE;
381
382 --
383 -- Constraints for table `sold_pets`
384 --
385 ALTER TABLE `sold_pets`
386 ADD CONSTRAINT `sold_pets_ibfk_1` FOREIGN KEY (`pet_id`) REFERENCES `pets` (`pet_id`) ON DELETE CASCADE,
387 ADD CONSTRAINT `sold_pets_ibfk_2` FOREIGN KEY (`sd_id`) REFERENCES `sales_details` (`sd_id`) ON DELETE CASCADE;
388
389 --
390 -- Constraints for table `sold_products`
391 --
392 ALTER TABLE `sold_products`
393 ADD CONSTRAINT `sold_products_ibfk_1` FOREIGN KEY (`sd_id`) REFERENCES `sales_details` (`sd_id`) ON DELETE CASCADE,
394 ADD CONSTRAINT `sold_products_ibfk_2` FOREIGN KEY (`pp_id`) REFERENCES `pet_products` (`pp_id`) ON DELETE CASCADE;
395 COMMIT;
396
397 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
398 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
399 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
